package Entities;

import java.sql.SQLException;
import java.util.ArrayList;
import Entities.Purchase_order;
import java.sql.Connection;
import digitshoplogic.DBAccessSingleton;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Customer {
	public int cardNo;
	public String password;
	public String name;
	public String phoneNo;
	public String email;
	public String ZIP;
	public String city;
	public String address;
	//ArrayList<Purchase_order> purchase_order = new ArrayList<Purchase_order>();

    public static final Customer getCustomerByCardNo(int cardNo) throws SQLException {
        Customer customer = new Customer();
        String cardNoStr = Integer.toString(cardNo);
        Connection con = DBAccessSingleton.getInstance().getConnection();
        String queryStr = "select * from customer where customer.cardNo =" + cardNoStr ;
        ResultSet rs = DBAccessSingleton.getInstance().getStatement().executeQuery(queryStr);

         while (rs.next()) {
                customer.cardNo = rs.getInt(1);
                customer.password = rs.getString(2);
                customer.name = rs.getString(3);
                customer.phoneNo = rs.getString(4);
                customer.email = rs.getString(5);
                customer.ZIP = rs.getString(6);
                customer.city = rs.getString(7);
                customer.address = rs.getString(8);
            } 
            rs.close();
            return customer;
        }
     public static final void addCustomerByObj(Customer customer) throws SQLException{

         PreparedStatement  ps;
         Connection con = DBAccessSingleton.getInstance().getConnection();
         ps = con.prepareStatement("INSERT INTO customer VALUES (?,?,?,?,?,?,?,?)");

	 ps.setInt(1, customer.cardNo);
         ps.setString(2, customer.password);
         ps.setString(3, customer.name);
         ps.setString(4, customer.phoneNo);
         ps.setString(5, customer.email);
         ps.setString(6, customer.ZIP);
         ps.setString(7, customer.city);
         ps.setString(8, customer.address);

         ps.executeUpdate();
         ps.close();

     }

     public static final void deleteCustomerByNo(int cardNo) throws SQLException{
        
         PreparedStatement  ps;
         Connection con = DBAccessSingleton.getInstance().getConnection();
         ps = con.prepareStatement("DELETE FROM customer WHERE customer.cardNo= ?");
	 ps.setInt(1, cardNo);

         ps.executeUpdate();
         ps.close();
     }
     
     public static final void updateCustomerByObj(Customer customer) throws SQLException{

         PreparedStatement  ps;
         Connection con = DBAccessSingleton.getInstance().getConnection();
         ps = con.prepareStatement("UPDATE customer SET "
                 + "customer.password = ?, "
                 + "customer.name = ?, "
                 + "customer.phoneNo = ?, "
                 + "customer.email = ?, "
                 + "customer.ZIP = ?, "
                 + "customer.city = ?,"
                 + "customer.address = ? WHERE customer.cardNo = ?");

	 ps.setInt(8, customer.cardNo);
         ps.setString(1, customer.password);
         ps.setString(2, customer.name);
         ps.setString(3, customer.phoneNo);
         ps.setString(4, customer.email);
         ps.setString(5, customer.ZIP);
         ps.setString(6, customer.city);
         ps.setString(7, customer.address);

         ps.executeUpdate();
         ps.close();

     }


     //UPDATE shop_db.customer SET `password` = 'pass222', `name` = 'n22', `phoneNo` = 'ph222ss', `email` = 'e@ss', `ZIP` = 'zip 20ss', `city` = 'burnabys' WHERE `cardNo` = 2;

    public String getZIP() {
        return ZIP;
    }

    public void setZIP(String ZIP) {
        this.ZIP = ZIP;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public int getCardNo() {
        return cardNo;
    }

    public void setCardNo(int cardNo) {
        this.cardNo = cardNo;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getPhoneNo() {
        return phoneNo;
    }

    public void setPhoneNo(String phoneNo) {
        this.phoneNo = phoneNo;
    }
   

}